MySQL 数据库 之 外键 & SQL查询语句详解 |
您所在的位置：网站首页 › iqoo8 压感 对比 3dtouch › MySQL 数据库 之 外键 & SQL查询语句详解 |
MySQL 数据库 之 外键 & SQL查询语句详解
|
外键
表与表之间关系
一对多
多对多
一对一
没有关系
操作表的SQL语句（ALTER）
SQL 查询关键字
查询语句之 where
查询语句之分组 group by
分组后筛选 having
去重 distinct
排序 order by
限制查询数据条数 limit
正则查询 regexp
聚合函数函数 max min sum avg count
连接字段 concat
内容详细
外键
什么是外键？ 用于建立表与表之间联系的字段，也是一种约束条件， 在 SQL 语句中，建立外键的关键字是 ： foreign key(本表字段名) references 被关联的表名(id) foreign key(dep_id) references dep(id)为什么要有外键？ 假设有这样一张员工信息数据表： ![image-20220221151359232] 上面这张表有以下缺陷： 1、表的重点不突出，到底是员工表还是部门表 2、表中部门和部门信息两个字段的数据很多在重复 3、表的拓展性极差，只要教学部改一个名字，整张表都要跟着改掉，牵一发而动全身如何建立外键？ 解决以上表的缺陷： 将上述一张表拆分成两张表，然后通过给一个表添加一个外键字段，将两张表联系起来
外键字段>>>:部门编号 其实就是用来标识表与表之间的数据关系 简单的理解为该字段可以让你去到其他表中查找数据 表与表之间关系表关系总共就四种： 一对多、多对多、一对一、没有关系 判断表关系的方式:换位思考 补充：级联更新，级联删除，也就是两表之间的数据改动可以进行同步 on update cascade # 级联更新 on delete cascade, # 级联删除 1.一对多 以员工和部门表为例 先站在员工表的基础之上 问:一个员工信息能否对应多个部门信息 答:不可以 再站在部门表的基础之上 问:一个部门信息能否对应多个员工信息 答:可以 结论:一个可以一个不可以 那么表关系就是"一对多" 员工表是多 部门表是一 """ 针对一对多的表关系 外键字段建在多的一方 """ # 表关系没有'多对一'一说 都是'一对多'使用SQL语句建立真正意义上的表关系: 注意：先创建不含外键字段的基本表，再添加外键字段 # 先创建被关联的表 部门表 create table dep( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(254) ); # 再创建含有外键的表 员工信息表 create table emp( id int primary key auto_increment, name varchar(32), age int, dep_id int, foreign key(dep_id) references dep(id) ); 2.多对多关系注意：多对多关系的表需要建立第三个表来存放两表之间的对应关系 以书籍表与作者表为例 先站在书籍表的基础之上 问:一个书籍信息能否对应多个作者信息 答:可以 再站在作者表的基础之上 问:一个作者信息能否对应多个书籍信息 答:可以 结论:两个都可以 那么表关系就是"多对多" # 多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定) # 先创建两张多对多关系的表 create table book( id int primary key auto_increment, title varchar(32), price float(6,2) ); create table author( id int primary key auto_increment, name varchar(32), age int ); # 创建第三张表存放两表之间的对应关系 create table book2author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade # 级联更新 on delete cascade, # 级联删除 foreign key(book_id) references book(id) on update cascade # 级联更新 on delete cascade # 级联删除 ); 3.一对一表关系一对一与一对多的区别是，一对一外键字段必须唯一 作者表与作者详情表 先站在作者表的基础之上 问:一个作者信息能否对应多个作者详情信息 答:不可以 再站在作者详情表的基础之上 问:一个作者详情信息能否对应多个作者信息 答:不可以 结论:两个都不可以 那么表关系可能是"一对一"或者"没有关系" # 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中SQL 语句创建： # 表一 create table author( id int primary key auto_increment, name varchar(32), age int, author_id int unique, # 一对一与一对多的区别是，一对一外键字段必须唯一 foreign key(author_id) references author_detail(id) on update cascade # 级联更新 on delete cascade # 级联删除 ); # 表二 create table author_detail( id int primary key auto_increment, phone varchar(32), address varchar(32) );**补充： ** 1.在创建表的时候 需要先创建被关联表(没有外键字段的表)2.在插入新数据的时候 应该先确保被关联表中有数据3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据4.在修改和删除被关联表中的数据的时候 无法直接操作如果想要数据之间自动修改和删除需要添加额外的配置 由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度所以在实际开发项目中 有时候并不会使用外键创建表关系而是通过SQL语句层面 建立逻辑意义上的表关系eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql 操作表的SQL语句（ALTER） show tables; desc 表名; create table t1(id int); alter table t1 change id nid int; drop table t1; 语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！ ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; SQL 查询关键字 数据准备： # 数据准备 create table emp( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('呵呵','female',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); 查询关键字之select与from from控制的是查询哪张表 select控制的是查询表里面的哪些字段 select * from emp; select id,name from emp; 查询关键字之where筛选 where筛选功能 """ 模糊查询:没有明确的筛选条件 关键字:like 关键符号: %:匹配任意个数任意字符 _:匹配单个个数任意字符 show variables like '%mode%se'; """ # 1.查询id大于等于3小于等于6的数据 select id,name from emp where id >= 3 and id >>聚合查询(聚集到一起合成为一个结果) # 每个部门的最高工资 select post,max(salary) from emp group by post; 补充:在显示的时候还可以给字段取别名 select post as '部门',max(salary) as '最高工资' from emp group by post; as也可以省略 但是不推荐省 因为寓意不明确 # 每个部门的最低工资 select post,min(salary) from emp group by post; # 每个部门的平均工资 select post,avg(salary) from emp group by post; # 每个部门的工资总和 select post,sum(salary) from emp group by post; # 每个部门的人数 select post,count(id) from emp group by post; 统计的时候只要是非空字段 效果都是一致的 这里显示age，salary，id最后演示特殊情况post_comment**补充： ** # group_concat 分组之后使用 如果真的需要获取分组意外的数据字段 可以使用group_concat() # 每个部门的员工姓名 select post,group_concat(name) from emp group by post; select post,group_concat(name,'|',sex) from emp group by post; # concat 不分组使用 select concat(name,sex) from emp; select concat(name,'|',sex) from emp; having过滤 """ where与having都是筛选功能 但是有区别 where在分组之前对数据进行筛选 having在分组之后对数据进行筛选 我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表 """ select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000; 关键字之distinct去重 # 对有重复的展示数据进行去重操作 一定要是重复的数据 select distinct id,age from emp; select distinct post from emp; 关键字之order by排序 select * from emp order by salary asc; #默认升序排 select * from emp order by salary desc; #降序排 #先按照age降序排，在年轻相同的情况下再按照薪资升序排 select * from emp order by age desc,salary asc; # 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序 select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc; 关键字之limit分页 # 限制展示条数 select * from emp limit 3; # 查询工资最高的人的详细信息 select * from emp order by salary desc limit 1; # 分页显示 select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置 select * from emp limit 5,5; 关键字之regexp正则 select * from emp where name regexp '^j.*(n|y)$'; 判断表关系 # 班级表 cid caption # 学生表 sid sname gender class_id # 老师表 tid tname # 课程表 cid cname teacher_id # 成绩表 sid student_id course_id number 1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
|
【本文地址】
今日新闻 |
推荐新闻 |